import json, pprint, sqlite3

def format(value):
    if value is None or isinstance(value, (int, long, float, str, unicode)):
        return value
    else:
        return "json:" + json.dumps(value, separators=(',',':'))

def unformat(value):
    if isinstance(value, (str, unicode)) and value.startswith("json:"):
        value = json.loads(value[5:])
        if isinstance(value, dict):
            # json returns unicode keys which are invalid for kwds
            value = dict((str(key), value) for key, value in value.iteritems())
    return value

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = unformat(row[idx])
    return d

class ConnWrapper:
    def __init__(self, conn):
        conn.row_factory = dict_factory
        self.conn = conn
    def execute(self, sql, *args):
        return self.conn.execute(sql, args)
    def executemany(self, sql, args_list):
        return self.conn.executemany(sql, args_list)
    def commit(self):
        self.conn.commit()
    def print_sql(self, sql, *args):
        for row in self.execute(sql, *args):
            print row

class DBWrapper(ConnWrapper):
    def __init__(self, conn):
        if isinstance(conn, str):
            conn = sqlite3.connect(conn)
        ConnWrapper.__init__(self, conn)
    def __getattr__(self, name):
        self.execute("create table if not exists %s (dummy)" % name)
        return TableWrapper(self.conn, name)

class TableWrapper(ConnWrapper):

    def __init__(self, conn, name):
        ConnWrapper.__init__(self, conn)
        self.name = name

    def insert(self, data):
        if isinstance(data, dict):
            data = [data]
        cols = set()
        for datum in data:
            cols.update(datum.keys())
        if not cols:
            return
        self.ensure_columns(cols)
        cols = tuple(cols)
        def to_insert():
            for datum in data:
                yield tuple(format(datum.get(col, None)) for col in cols)
        sql = "insert into %s (%s) values (%s)" % (self.name, ",".join(cols), ",".join(["?"] * len(cols)))
        self.executemany(sql, to_insert())
    
    def find(self, limit=None, *args, **kwds):
        clauses = list(args)
        for key, value in kwds.items():
            clauses.append("%s = '%s'" % (key, value))
        sql = "select * from %s where %s" % (self.name, " and ".join(clauses))
        if limit is not None:
            sql += " limit %s" % limit
        return self.execute(sql)
    
    def find_one(self, *args, **kwds):
        return self.find(limit=1, *args, **kwds)
        
    def ensure_columns(self, cols):
        existing_cols = set(row['name'] for row in self.execute("pragma table_info (%s)" % self.name))
        for col in set(cols).difference(existing_cols):
            self.execute("alter table %s add column %s" % (self.name, col))
    
    def ensure_index(self, col=None, **kwds):
        if col is None:
            assert len(kwds) == 1
            col = kwds.popitem()[0]
        self.ensure_columns([col])
        self.execute("create index if not exists __index_%s on %s (%s)" % (col, self.name, col))
